package org.zjx.db;

import com.alibaba.fastjson2.JSON;
import org.zjx.entity.Transfer;
import org.zjx.route.RouteType;

import java.util.List;

public class TransferDB {
    private static boolean isTableCreated = false;
    private final SQLiteHelper dbHelper;

    public TransferDB() {
        dbHelper = SQLiteHelper.getSingleton();
        if (!isTableCreated) {
            createTable();
            isTableCreated = true;
        }
    }

    public void createTable() {
        String createTable = "CREATE TABLE IF NOT EXISTS transfers (\n"
                + "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
                + "    type TEXT NOT NULL,\n"
                + "    fromSceneId INTEGER NOT NULL,\n"
                + "    fromX INTEGER NOT NULL,\n"
                + "    fromY INTEGER NOT NULL,\n"
                + "    toSceneId INTEGER NOT NULL,\n"
                + "    toX INTEGER NOT NULL,\n"
                + "    toY INTEGER NOT NULL,\n"
                + "    clickX INTEGER NOT NULL,\n"
                + "    clickY INTEGER NOT NULL,\n"
                + "    npcId INTEGER DEFAULT 0,\n"
                + "    dialogues TEXT\n"
                + ");";
        dbHelper.createTable(createTable);
    }

    public long insert(Transfer transfer) {
        String sql = "INSERT INTO transfers(type, fromSceneId, fromX, fromY, "
                + "toSceneId, toX, toY, clickX, clickY, npcId, dialogues) "
                + "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        Object[] params = {
                transfer.getType().name(),
                transfer.getFromSceneId(),
                transfer.getFromX(),
                transfer.getFromY(),
                transfer.getToSceneId(),
                transfer.getToX(),
                transfer.getToY(),
                transfer.getClickX(),
                transfer.getClickY(),
                transfer.getNpcId(),
                transfer.getDialogues() != null && !transfer.getDialogues().isEmpty()
                        ? JSON.toJSONString(transfer.getDialogues())
                        : null
        };

        return dbHelper.executeUpdate(sql, params);
    }

    public void update(Transfer transfer) {
        String sql = "UPDATE transfers SET type = ?, fromSceneId = ?, "
                + "fromX = ?, fromY = ?, toSceneId = ?, toX = ?, toY = ?, "
                + "clickX = ?, clickY = ?, npcId = ?, dialogues = ? WHERE id = ?";

        Object[] params = {
                transfer.getType().name(),
                transfer.getFromSceneId(),
                transfer.getFromX(),
                transfer.getFromY(),
                transfer.getToSceneId(),
                transfer.getToX(),
                transfer.getToY(),
                transfer.getClickX(),
                transfer.getClickY(),
                transfer.getNpcId(),
                transfer.getDialogues() != null && !transfer.getDialogues().isEmpty()
                        ? JSON.toJSONString(transfer.getDialogues())
                        : null,
                transfer.getId()
        };

        dbHelper.executeUpdate(sql, params);
    }

    public void deleteById(int id) {
        String sql = "DELETE FROM transfers WHERE id = ?";
        Object[] params = {id};
        dbHelper.executeUpdate(sql, params);
    }

    public Transfer findById(int id) {
        String sql = "SELECT * FROM transfers WHERE id = ?";
        Object[] params = {id};
        List<Transfer> transfers = dbHelper.executeQuery(sql, params, Transfer.class);
        return transfers.isEmpty() ? null : transfers.get(0);
    }

    public List<Transfer> findByFromSceneId(int fromSceneId) {
        String sql = "SELECT * FROM transfers WHERE fromSceneId = ?";
        Object[] params = {fromSceneId};
        return dbHelper.executeQuery(sql, params, Transfer.class);
    }

    public List<Transfer> findByToSceneId(int toSceneId) {
        String sql = "SELECT * FROM transfers WHERE toSceneId = ?";
        Object[] params = {toSceneId};
        return dbHelper.executeQuery(sql, params, Transfer.class);
    }

    public List<Transfer> findByType(RouteType type) {
        String sql = "SELECT * FROM transfers WHERE type = ?";
        Object[] params = {type.name()};
        return dbHelper.executeQuery(sql, params, Transfer.class);
    }

    public List<Transfer> findByNpcId(int npcId) {
        String sql = "SELECT * FROM transfers WHERE npcId = ?";
        Object[] params = {npcId};
        return dbHelper.executeQuery(sql, params, Transfer.class);
    }

    public List<Transfer> findAll() {
        String sql = "SELECT * FROM transfers";
        return dbHelper.executeQuery(sql, null, Transfer.class);
    }

    public void clearData() {
        dbHelper.clearData("transfers");
    }

    public void dropTable() {
        dbHelper.dropTable("transfers");
    }
}